import sqlite3
from typing import Optional, Dict, List

class EquipmentDB:
    def __init__(self, db_path: str = 'equipment.db'):
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self._create_table()

    def _create_table(self):
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS equipment (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                Ti REAL,
                Jin REAL,
                Yu REAL,
                Min REAL,
                Shi REAL,
                QiXue REAL,
                ZhenQi REAL,
                ZuiDaWaiGong REAL,
                ZuiXiaoWaiGong REAL,
                FangYu REAL,
                JingZun REAL,
                JingZunHuang REAL,
                HuiXin REAL,
                HuiXinHuang REAL,
                HuiYi REAL,
                HuiYiHuang REAL,
                ShuXingZuiDa REAL,
                ShuXingZuiXiao REAL
            )
        ''')
        self.conn.commit()

    def add_equipment(self, data: Dict[str, str]) -> int:
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?'] * len(data))
        self.cursor.execute(
            f'INSERT INTO equipment ({columns}) VALUES ({placeholders})',
            tuple(data.values())
        )
        self.conn.commit()
        return self.cursor.lastrowid

    def get_equipment(self, equipment_id: int) -> Optional[Dict[str, str]]:
        self.cursor.execute('SELECT * FROM equipment WHERE id = ?', (equipment_id,))
        row = self.cursor.fetchone()
        if row:
            columns = [description[0] for description in self.cursor.description]
            return dict(zip(columns, row))
        return None

    def update_equipment(self, equipment_id: int, data: Dict[str, str]) -> bool:
        set_clause = ', '.join([f'{key} = ?' for key in data.keys()])
        values = tuple(data.values()) + (equipment_id,)
        self.cursor.execute(
            f'UPDATE equipment SET {set_clause} WHERE id = ?',
            values
        )
        self.conn.commit()
        return self.cursor.rowcount > 0

    def delete_equipment(self, equipment_id: int) -> bool:
        self.cursor.execute('DELETE FROM equipment WHERE id = ?', (equipment_id,))
        self.conn.commit()
        return self.cursor.rowcount > 0

    def get_all_equipments(self) -> List[Dict[str, str]]:
        self.cursor.execute('SELECT * FROM equipment')
        columns = [description[0] for description in self.cursor.description]
        return [dict(zip(columns, row)) for row in self.cursor.fetchall()]

    def calculate_stats(self, current: dict, new: dict, result: dict) -> dict:
        """
        计算新装备替换当前装备后的整体属性
        :param current: 当前装备属性字典
        :param new: 新装备属性字典
        :param result: 旧数据字典
        :return: 更新后的属性字典
        """
        
        # 体
        if 'Ti' in current:
            result['QiXue'] = float(result['QiXue']) - float(current.get('Ti', 0)) * 60  # 减去旧值
            result['Ti'] = float(result['Ti']) - float(current.get('Ti', 0))  # 减去旧值
        if 'Ti' in new:
            result['QiXue'] = float(result['QiXue']) + float(new['Ti']) * 60  # 加上新值
            result['Ti'] = float(result['Ti']) + float(new['Ti'])  # 加上新值

        # 劲 1劲=0.246最小外功攻击 + 1.315最大外功攻击
        if 'Jin' in current:
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) - float(current.get('Jin')) * 1.315  # 减去旧值
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) - float(current.get('Jin')) * 0.246  # 减去旧值
            result['Jin'] = float(result['Jin']) - float(current.get('Jin'))  # 减去旧值
        if 'Jin' in new:
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) + float(new['Jin']) * 1.315  # 加上新值
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) + float(new['Jin']) * 0.246  # 加上新值
            result['Jin'] = float(result['Jin']) + float(new['Jin'])  # 加上新值

        # 御 1御=17气血值 + 0.5外功防御
        if 'Yu' in current:
            result['FangYu'] = float(result['FangYu']) - float(current.get('Yu')) * 0.5  # 减去旧值
            result['QiXue'] = float(result['QiXue']) - float(current.get('Yu')) * 17  # 减去旧值
            result['Yu'] = float(result['Yu']) - float(current.get('Yu'))  # 减去旧值
        if 'Yu' in new:
            result['FangYu'] = float(result['FangYu']) + float(new['Yu']) * 0.5  # 加上新值
            result['QiXue'] = float(result['QiXue']) + float(new['Yu']) * 17  # 加上新值
            result['Yu'] = float(result['Yu']) + float(new['Yu'])  # 加上新值
        # 敏 1敏≈1最小外功攻击 + 0.075%会心率
        if 'Min' in current:
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) - float(current.get('Min')) * 1  # 减去旧值
            result['HuiXin'] = float(result['HuiXin']) - float(current.get('Min')) * 0.075  # 减去旧值
            result['HuiXinHuang'] = float(result['HuiXinHuang']) - float(current.get('Min')) * 0.075  # 减去旧值
            result['Min'] = float(result['Min']) - float(current.get('Min'))  # 减去旧值
        if 'Min' in new:
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) + float(new['Min']) * 1  # 加上新值
            result['HuiXin'] = float(result['HuiXin']) + float(new['Min']) * 0.075  # 加上新值
            result['HuiXinHuang'] = float(result['HuiXinHuang']) + float(new['Min']) * 0.075  # 加上新值
            result['Min'] = float(result['Min']) + float(new['Min'])  # 加上新值
        # 势 1势≈0.9最大外功攻击 + 0.04%会意率
        if 'Shi' in current:
            print(result['ZuiDaWaiGong'])
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) - float(current.get('Shi')) * 0.9  # 减去旧值
            result['HuiYi'] = float(result['HuiYi']) - float(current.get('Shi')) * 0.04  # 减去旧值
            result['HuiYiHuang'] = float(result['HuiYiHuang']) - float(current.get('Shi')) * 0.04  # 减去旧值
            result['Shi'] = float(result['Shi']) - float(current.get('Shi'))  # 减去旧值
        if 'Shi' in new:
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) + float(new['Shi']) * 0.9  # 加上新值
            result['HuiYi'] = float(result['HuiYi']) + float(new['Shi']) * 0.04  # 加上新值
            result['HuiYiHuang'] = float(result['HuiYiHuang']) + float(new['Shi']) * 0.04  # 加上新值
            result['Shi'] = float(result['Shi']) + float(new['Shi'])  # 加上新值
        # 气血
        if 'QiXue' in current:
            result['QiXue'] = float(result['QiXue']) - float(current.get('QiXue'))  # 减去旧值
        if 'QiXue' in new:
            result['QiXue'] = float(result['QiXue']) + float(new['QiXue'])  # 加上新值
        # 最小外功
        if 'ZuiXiaoWaiGong' in current:
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) - float(current.get('ZuiXiaoWaiGong'))  # 减去旧值
        if 'ZuiXiaoWaiGong' in new:
            result['ZuiXiaoWaiGong'] = float(result['ZuiXiaoWaiGong']) + float(new['ZuiXiaoWaiGong'])  # 加上新值
        # 最大外功
        if 'ZuiDaWaiGong' in current:
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) - float(current.get('ZuiDaWaiGong'))  # 减去旧值
        if 'ZuiDaWaiGong' in new:
            result['ZuiDaWaiGong'] = float(result['ZuiDaWaiGong']) + float(new['ZuiDaWaiGong'])  # 加上新值
        # 外功防御
        if 'FangYu' in current:
            result['FangYu'] = float(result['FangYu']) - float(current.get('FangYu'))  # 减去旧值
        if 'FangYu' in new:
            result['FangYu'] = float(result['FangYu']) + float(new['FangYu'])  # 加上新值
        # 精准
        if 'JingZun' in current:
            result['JingZun'] = float(result['JingZun']) - float(current.get('JingZun'))  # 减去旧值
            result['JingZunHuang'] = float(result['JingZunHuang']) - float(current.get('JingZun'))  # 减去旧值
        if 'JingZun' in new:
            result['JingZun'] = float(result['JingZun']) + float(new['JingZun'])  # 加上新值
            result['JingZunHuang'] = float(result['JingZunHuang']) + float(new['JingZun'])  # 加上新值
        # 会心
        if 'HuiXin' in current:
            result['HuiXin'] = float(result['HuiXin']) - float(current.get('HuiXin'))  # 减去旧值
            result['HuiXinHuang'] = float(result['HuiXinHuang']) - float(current.get('HuiXin'))  # 减去旧值
        if 'HuiXin' in new:
            result['HuiXin'] = float(result['HuiXin']) + float(new['HuiXin'])  # 加上新值
            result['HuiXinHuang'] = float(result['HuiXinHuang']) + float(new['HuiXin'])  # 加上新值
        # 会意
        if 'HuiYi' in current:
            result['HuiYi'] = float(result['HuiYi']) - float(current.get('HuiYi'))  # 减去旧值
            result['HuiYiHuang'] = float(result['HuiYiHuang']) - float(current.get('HuiYi'))  # 减去旧值
        if 'HuiYi' in new:
            result['HuiYi'] = float(result['HuiYi']) + float(new['HuiYi'])  # 加上新值
            result['HuiYiHuang'] = float(result['HuiYiHuang']) + float(new['HuiYi'])  # 加上新值
        # 属性攻击最大
        if 'ShuXingZuiDa' in current:
            result['ShuXingZuiDa'] = float(result['ShuXingZuiDa']) - float(current.get('ShuXingZuiDa'))  # 减去旧值
        if 'ShuXingZuiDa' in new:
            result['ShuXingZuiDa'] = float(result['ShuXingZuiDa']) + float(new['ShuXingZuiDa'])  # 加上新值
        # 属性攻击最小
        if 'ShuXingZuiXiao' in current:
            result['ShuXingZuiXiao'] = float(result['ShuXingZuiXiao']) - float(current.get('ShuXingZuiXiao'))  # 减去旧值
        if 'ShuXingZuiXiao' in new:
            result['ShuXingZuiXiao'] = float(result['ShuXingZuiXiao']) + float(new['ShuXingZuiXiao'])  # 加上新值

        return result
        
    def close(self):
        self.conn.close()